﻿using Newtonsoft.Json;
using QskjBaseDataToDb.Dto;
using QskjLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QskjBaseDataToDb.Service
{
    public class ZakjBusDataUpdate
    {
        public static void UpdateCarData()
        {
            try
            {
                //string dtStrPre = DateTime.Now.AddDays(-2).ToString("yyyyMMdd");
                //string sqlPre = $@"drop table if exists qskjrecord.B_BusZakj{dtStrPre}";
                //MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, sqlPre);
                string dtStr = DateTime.Now.ToString("yyyyMMdd");
                if (DateTime.Now.Hour < 7)
                {
                    string exists = $@"SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'qskjrecord' AND TABLE_NAME = 'B_BusZakj{dtStr}';";
                    object resExi = MySqlHelper.ExecuteScalar(CommandType.Text, exists);
                    if (resExi == null || resExi == DBNull.Value)
                    {
                        string sql = $@"create table IF NOT EXISTS qskjrecord.B_BusZakj{dtStr}
                        (
                           id                   bigint not null auto_increment,
                           terminalkey            varchar(50),
                           license_plate            varchar(50),
                           color          varchar(50),
                           companyname                  varchar(50),
                           filialename          varchar(50),
                           supplier      varchar(50),
                           channel varchar(50), 
                           equipment_type varchar(50), 
                           purchase_date varchar(50), 
                           channelname varchar(200), 
                           car_num            varchar(50),
                           primary key (id)
                        );";
                        MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql);
                    }
                }
                string sqlCount = $@"select count(*) from qskjrecord.B_BusZakj{dtStr}";
                if (Convert.ToInt32(MySqlHelper.ExecuteScalar(CommandType.Text, sqlCount)) > 0)
                {
                    Console.WriteLine(DateTime.Now + "读取中安大巴车信息 数据已读取成功");
                    return;
                }
                //读取中安科技大巴车数据
                var str = HttpHelper.GetZakjBus().Result;
                ZakjBus obj = JsonConvert.DeserializeObject<ZakjBus>(str);
                foreach (var item in obj.data.data)
                {
                    string insertSql = $@"insert into qskjrecord.B_BusZakj{dtStr}(terminalkey,license_plate,color,companyname,filialename,supplier,channel,equipment_type,purchase_date,channelname,car_num)
                                        values('{item.terminalkey}','{item.license_plate.Replace("-", "")}','{item.color}','{item.companyname}','{item.filialename}','{item.supplier}','{item.channel}','{item.equipment_type}','{item.purchase_date}','{item.channelname}','{item.license_plate.Replace("-", "").Substring(1)}')";
                    MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, insertSql);
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("读取中安大巴车信息异常：" + e.Message);
            }
        }
    }
}
